package com.wy.tingshu.servlet.publish;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.wy.tingshu.util.DBUtil;
import com.wy.tingshu.util.Log;
import lombok.SneakyThrows;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Predicate;

/**
 * 1.提供音频数据的URL；2.统一的数据接口
 **/
//2.统一的数据接口
@WebServlet("/album-detail.json")
public class AlbumDetailServlet extends HttpServlet {
    private static class TrackView{
        public Integer tid;
        public String title;
        @SneakyThrows
        public TrackView(ResultSet rs) {
            this.tid= rs.getInt("tid");
            this.title=rs.getString("title");
        }
    }
    private static class ResultView{
        public Integer aid;
        public String username;
        public String cover;
        public String title;
        public List<TrackView> trackList=new ArrayList<>();

        @SneakyThrows
        public void set(ResultSet rs) {
            this.aid= rs.getInt("aid");
            this.username=rs.getString("username");
            this.cover=rs.getString("cover");
            this.title=rs.getString("title");
        }
    }
    @SneakyThrows
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String aid = req.getParameter("aid");
        ResultView resultView=new ResultView();
        try (Connection c= DBUtil.connection()){
            {
                //albums+users连表查询：aid=?,a.uid=u.uid时，从albums专辑表中查出对应专辑的专辑主键aid,用户名字，专辑名称，封面
                String sql="select aid,username,title,cover from albums a join users u on a.uid=u.uid where aid=?";
                try (PreparedStatement ps=c.prepareStatement(sql)){
                    ps.setString(1,aid);
                    Log.println("执行SQL:"+ps);
                    try (ResultSet rs=ps.executeQuery()){
                        if(!rs.next()){
                            throw new RuntimeException("aid对应的专辑不存在 404");
                        }
                        resultView.set(rs);
                    }
                }
            }
            List<String> tidList=new ArrayList<>();
            {
                //专辑主键aid=?时从关系表中找出所有对应tid（音频主键）【一个专辑多个音频】
                String sql="select tid from relations where aid=?";
                try (PreparedStatement ps=c.prepareStatement(sql)){
                    ps.setString(1,aid);
                    Log.println("执行SQL:"+ps);
                    try (ResultSet rs=ps.executeQuery()){
                        while (rs.next()){
                            tidList.add(rs.getString("tid"));
                        }
                    }
                }
            }
            if(tidList.isEmpty()){
                throw new RuntimeException("这个专辑没有绑定任何音频数据");
            }
            {
                //在音频表是%s数据内容时，查找音频表中相关音频对应的的tid,title【%s:aid=?的专辑对应的所有tid】
                String sqlFormat="select tid,title from tracks where tid in (%s) order by tid desc";
                String s = String.join(", ", tidList);
                String sql=String.format(sqlFormat,s);
                try (PreparedStatement ps=c.prepareStatement(sql)){
                    Log.println("执行SQL:"+ps);
                    try (ResultSet rs=ps.executeQuery()){
                        while (rs.next()){
                            TrackView view=new TrackView(rs);
                            resultView.trackList.add(view);
                        }
                    }
                }
            }
        }
        ObjectMapper objectMapper=new ObjectMapper();
        String json=objectMapper.writeValueAsString(resultView);
        Log.println("响应JSON："+json);
        resp.setCharacterEncoding("utf-8");
        resp.setContentType("application/json");
        resp.getWriter().println(json);
    }
}
